package dblayer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import modellayer.Invoice;

/**
 * 
 * @author Gruppe 3
 *
 */

public class DBInvoice implements IFDBInvoice {
	
	private Connection con;
	
	public DBInvoice()
	{
		con = DBConnection.getInstance().getDBcon();
	}

	/**
 	 * An ArrayList with all Invoice's
 	 */
	public ArrayList<Invoice> getAllInvoices() {
		return miscWhere("");
	}

	/**
     * Select a single Invoice
     */
	public Invoice selectSingleInvoice(int invoiceID) throws SQLException 
	{
		String wClause = "invoiceID = "+invoiceID;
        return singleWhere(wClause);
	}

	/**
     * Insert a Invoice to the DB
     */
	public int insertInvoice(Invoice i) throws SQLException 
	{
		PreparedStatement pstmt = null;
		int controlInt = -1;
		String insert = "insert into Invoice(invoiceNo, amount, paymentDate)"
                      + "values (?,?,?)";
   	 	//System.out.println(insert);
   	 try {
   		 pstmt = con.prepareStatement(insert);
            pstmt.setInt(1, i.getInvoiceNo());
            pstmt.setInt(2, i.getAmount());
            pstmt.setString(3, i.getPaymentDate());
            controlInt = pstmt.executeUpdate();
        } catch (SQLException sqlE) {
            System.out.println("SQL Error, Invoice not inserted");
            System.out.println(sqlE.getMessage());
        } catch (Exception e) {
            e.getMessage();
        }
		
   	 	return controlInt;
	}

	/**
     * Updates the Invoice db
     */
	public int updateInvoice(Invoice i) throws SQLException 
	{
		Invoice iObj = i;
		int controlInt = -1;
   	 PreparedStatement pstmt = null;

   	 String update = "UPDATE Invoice SET "
   	 		+ "invoiceNo = ?"
   	 		+ "paymentDate = ? "
   			+ "amount = ?"
   	 		+ "WHERE invoiceID = ?";
   	 
   	 try {
   		 pstmt = con.prepareStatement(update);
   		pstmt.setInt(1, iObj.getInvoiceNo());
   		 pstmt.setString(2, iObj.getPaymentDate());
   		 pstmt.setInt(3, iObj.getAmount());
   		pstmt.setInt(4, iObj.getInvoiceID());
   		 controlInt = pstmt.executeUpdate();
   		 con.commit();
   		 pstmt.close();
   		 con.close();
   	 } catch (SQLException sqlE) {
   		 System.out.println("SQL Error, Invoice not updated");
   		 System.out.println(sqlE.getMessage());
   	 } catch (Exception e) {
   		 e.getMessage();
   	 }
   	 return controlInt;
	}

	/**
     * Delete a Invoice from the database
     */
	public int deleteInvoice(Invoice i) throws SQLException 
	{
		PreparedStatement pstmt = null;
		int controlInt = -1;
		String delete = "DELETE FROM Invoice "
   	 		+ "WHERE invoiceID = ?";
   	 	//System.out.println(delete);
   	 try {
   		 	pstmt = con.prepareStatement(delete);
   		 	pstmt.setInt(1, i.getInvoiceID());
   		    controlInt = pstmt.executeUpdate();
   		 	con.commit();
   		 	pstmt.close();
   		 	con.close();
        } catch (SQLException sqlE) {
            System.out.println("SQL Error, Invoice not deleted");
            System.out.println(sqlE.getMessage());
        } catch (Exception e) {
            e.getMessage();
        }
   	 
   	 	return controlInt; 
	
	}
	
	
    /**
	 * If more than one Invoice is to be selected
	 * @param wClause
	 * @return ArrayList with Invoice objects
	 */
    private ArrayList<Invoice> miscWhere(String wClause)
	 {
   	 	ResultSet results;
	    ArrayList<Invoice> list = new ArrayList<Invoice>();	
		
	    String query = buildQuery(wClause);
   
	    try{ // read the invoices from the database
			Statement stmt = con.createStatement();
		 	stmt.setQueryTimeout(5);
		 	results = stmt.executeQuery(query);
		 	
			while( results.next() ){ // loop through all invoices and create them as objects
			    Invoice iObj = new Invoice();
			    iObj = buildInvoice(results);	
		        list.add(iObj);	
			 }//end while
			
			stmt.close();   
	     }//end try
	    catch(Exception e){
	 	 	System.out.println("Query exception - select: "+e);
		 	e.printStackTrace();
	 	 }
		 return list;
	 }

    /**
 	 * If only one invoice is to be selected
 	 * @param wClause
 	 * @return Invoice object
 	 */
 	 private Invoice singleWhere(String wClause)
 	 {
 	 	ResultSet results;
 	 	Invoice iObj = new Invoice();
 		        
 	 	String query = buildQuery(wClause);
 		//System.out.println(query);
 		
 		try{ 	
 			Statement stmt = con.createStatement();
 			stmt.setQueryTimeout(5);
 			results = stmt.executeQuery(query);
 			if( results.next() ){
 				iObj = buildInvoice(results);
 				stmt.close();
 			}else{ 
 				iObj = null;
 			}
 		}//end try	
 		
 		catch(Exception e){
 			System.out.println("Query exception: "+e);
 		}
 		return iObj;
 	 }
 	 
 	 
 	/**
  	 * Method to build the query
  	 * @param wClause
  	 * @return
  	 */
  	 private String buildQuery(String wClause)
  	 {
  	     String query="SELECT * FROM Invoice";
  		
  	 	if (wClause.length()>0)
  	 		query=query+" WHERE "+ wClause;
  	 		
  	 	return query;
  	 }
      
      /**
       * Builds a Invoice object from the data in the database
       * @param result
       * @return Invoice object
       * @throws SQLException
       */
      private Invoice buildInvoice(ResultSet result) throws SQLException {
    	  Invoice iObj = new Invoice();

     	 try {
     		 iObj.setInvoiceID(result.getInt("invoiceID"));
     		iObj.setInvoiceNo(result.getInt("invoiceNo"));
     		iObj.setPaymentDate(result.getString("paymentDate"));
     		iObj.setAmount(result.getInt("amount"));
     	 } catch (Exception e) {
     		 System.out.println("Error building Invoice object, "+e);
          }
          return iObj;
      }
	
}
